package com.ysd.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.ysd.entity.Newscategory;
import com.ysd.entity.Newsdetails;
import com.ysd.util.DBManager;

public class NewsdetailsDaoImpl { // 实现类,起名: 实体类名DaoImpl
	public List<Newsdetails> getNewsdetails(int nid) {
		List<Newsdetails> list = new ArrayList<Newsdetails>();
		// 1.建立连接
		Connection conn = DBManager.getConnection();
		Statement st = null;
		ResultSet rs = null;
		Newsdetails newsdetails = null;
		try {// 正常
				// 2.创建Statement对象
			st = conn.createStatement();
			// 3.sql语句
			String sql = "select * from newsdetails where newsCategoryId=" + nid;
			// 4.执行sql语句
			rs = st.executeQuery(sql);// execute执行Query查询
			// 5.遍历结果集
			while (rs.next()) {
				int newsId = rs.getInt("newsId");
				String newsTitle = rs.getString("newsTitle");
				String nwesContent = rs.getString("nwesContent");
				int newsCategoryId = rs.getInt("newsCategoryId");
				newsdetails = new Newsdetails(newsId, newsTitle, nwesContent, newsCategoryId);
				list.add(newsdetails);

			}
			return list;// 正常执行返回用户集合
		} catch (SQLException e) {// 异常
			e.printStackTrace();
			return null;// 异常执行返回空
		} finally {
			DBManager.closeAll(conn, st, rs);
		}
	}

	// 查询新闻上一条
	public List<Newsdetails> skipNewDetails(int aId) {
		List<Newsdetails> newsList = new ArrayList<Newsdetails>();
		Connection conn = DBManager.getConnection();
		Statement st = null;
		ResultSet rs = null;
		Newsdetails newsdetails = null;
		try {
			st = conn.createStatement();
			rs = st.executeQuery(
					"select * from newsdetails where newsId = (select newsId from newsdetails where newsId < " + aId
							+ " order by newsId desc limit 1)");

			while (rs.next()) {
				int newsId = rs.getInt("newsId");
				String newsTitle = rs.getString("newsTitle");
				String nwesContent = rs.getString("nwesContent");
				int newsCategoryId = rs.getInt("newsCategoryId");
				newsdetails = new Newsdetails(newsId, newsTitle, nwesContent, newsCategoryId);
				newsList.add(newsdetails);
			}
			return newsList;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		} finally {
			DBManager.closeAll(conn, st, rs);
		}
	}

	// 查询新闻的下一条信息
	public List<Newsdetails> skipNewsDetails(int bId) {
		List<Newsdetails> nList = new ArrayList<Newsdetails>();
		Connection conn = DBManager.getConnection();
		Statement st = null;
		ResultSet rs = null;
		Newsdetails news = null;
		try {
			st = conn.createStatement();
			rs = st.executeQuery(
					"select * from newsdetails where newsId = (select newsId from newsdetails where newsId > " + bId
							+ " order by newsId desc limit 1)");

			while (rs.next()) {
				int newsId = rs.getInt("newsId");
				String newsTitle = rs.getString("newsTitle");
				String nwesContent = rs.getString("nwesContent");
				int newsCategoryId = rs.getInt("newsCategoryId");
				news = new Newsdetails(newsId, newsTitle, nwesContent, newsCategoryId);
				nList.add(news);
			}
			return nList;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return null;
		} finally {
			DBManager.closeAll(conn, st, rs);
		}
	}

	// 显示more
	public List<Newsdetails> getNewsmore() {
		List<Newsdetails> list = new ArrayList<Newsdetails>();
		// 1.建立连接
		Connection conn = DBManager.getConnection();
		Statement st = null;
		ResultSet rs = null;
		Newsdetails newsdetails = null;
		try {// 正常
				// 2.创建Statement对象
			st = conn.createStatement();
			// 3.sql语句
			String sql = "select * from newsdetails";
			// 4.执行sql语句
			rs = st.executeQuery(sql);// execute执行Query查询
			// 5.遍历结果集
			while (rs.next()) {
				int newsId = rs.getInt("newsId");
				String newsTitle = rs.getString("newsTitle");
				String nwesContent = rs.getString("nwesContent");
				int newsCategoryId = rs.getInt("newsCategoryId");
				newsdetails = new Newsdetails(newsId, newsTitle, nwesContent, newsCategoryId);
				list.add(newsdetails);
			}
			return list;// 正常执行返回用户集合
		} catch (SQLException e) {// 异常
			e.printStackTrace();
			return null;// 异常执行返回空
		} finally {
			DBManager.closeAll(conn, st, rs);
		}
	}

	// 新闻详情
	public List<Newsdetails> getNewsContent(int neid) {
		List<Newsdetails> list = new ArrayList<Newsdetails>();
		// 1.建立连接
		Connection conn = DBManager.getConnection();
		Statement st = null;
		ResultSet rs = null;
		Newsdetails newsdetails = null;
		try {// 正常
				// 2.创建Statement对象
			st = conn.createStatement();
			// 3.sql语句
			String sql = "select * from newsdetails where newsId=" + neid;
			// 4.执行sql语句
			rs = st.executeQuery(sql);// execute执行Query查询
			// 5.遍历结果集
			while (rs.next()) {
				int newsId = rs.getInt("newsId");
				String newsTitle = rs.getString("newsTitle");
				String nwesContent = rs.getString("nwesContent");
				int newsCategoryId = rs.getInt("newsCategoryId");
				newsdetails = new Newsdetails(newsId, newsTitle, nwesContent, newsCategoryId);
				list.add(newsdetails);

			}
			return list;// 正常执行返回用户集合
		} catch (SQLException e) {// 异常
			e.printStackTrace();
			return null;// 异常执行返回空
		} finally {
			DBManager.closeAll(conn, st, rs);
		}
	}
	/***
	 *多条件查询新闻信息 
	 * 
	 * 
	 */
	
	public List<Newsdetails> getNewsDetailsByWhere(Newsdetails n) {
		List<Newsdetails> list = new ArrayList<Newsdetails>();
		// 1.建立连接
		Connection conn = DBManager.getConnection();
		Statement st = null;
		ResultSet rs = null;
		Newsdetails newsdetails = null;
		try {// 正常
				// 2.创建Statement对象
			st = conn.createStatement();
			// 3.sql语句
			String sql = "select n.*,ne.newsCategoryName from newsdetails n,newscategory ne where 1=1";//1=1是永真条件 (永远为true)写过一次之后就不用考虑where写在哪里了
			if (n.getNewsId()!=0) {//非空判定
				sql += " and newsId like '%"+n.getNewsId()+"%'";
			}
			if (n.getNewsTitle()!=null && !n.getNewsTitle().equals("")) {
				sql+=" and newsTitle like '%"+n.getNewsTitle()+"%'";
			}
			if (n.getNwesContent()!=null && !n.getNwesContent().equals("")) {
				sql+=" and nwesContent like '%"+n.getNwesContent()+"%'";
			}
			if (n.getNewsCategoryId()!=0) {
				sql+=" and n.newsCategoryId="+n.getNewsCategoryId();
			}
			sql+=" and n.newsCategoryId=ne.newsCategoryId";
			System.out.println(sql);//查看sql是否拼接成功
			// 4.执行sql语句
			rs = st.executeQuery(sql);// execute执行Query查询
			// 5.遍历结果集
			while (rs.next()) {
				int newsId = rs.getInt("newsId");
				String newsTitle = rs.getString("newsTitle");
				String nwesContent = rs.getString("nwesContent");
				int newsCategoryId = rs.getInt("newsCategoryId");
				String newsCategoryName= rs.getString("newsCategoryName");
				newsdetails = new Newsdetails(newsId, newsTitle, nwesContent, newsCategoryId, newsCategoryName);
				list.add(newsdetails);
			}
			return list;// 正常执行返回用户集合
		} catch (SQLException e) {// 异常
			e.printStackTrace();
			return null;// 异常执行返回空
		} finally {
			DBManager.closeAll(conn, st, rs);
		}
	}
	/***
	 *多条件分页查询新闻信息 
	 * 
	 * 
	 */
	
	public List<Newsdetails> getNewsDetailsByPage(Newsdetails n, int page, int size) {
		List<Newsdetails> list = new ArrayList<Newsdetails>();
		// 1.建立连接
		Connection conn = DBManager.getConnection();
		Statement st = null;
		ResultSet rs = null;
		Newsdetails newsdetails = null;
		try {// 正常
				// 2.创建Statement对象
			st = conn.createStatement();
			// 3.sql语句
			String sql = "select n.*,ne.newsCategoryName from newsdetails n,newscategory ne where 1=1";//1=1是永真条件 (永远为true)写过一次之后就不用考虑where写在哪里了
			if (n.getNewsId()!=0) {//非空判定
				sql += " and newsId like '%"+n.getNewsId()+"%'";
			}
			if (n.getNewsTitle()!=null && !n.getNewsTitle().equals("")) {
				sql+=" and newsTitle like '%"+n.getNewsTitle()+"%'";
			}
			if (n.getNwesContent()!=null && !n.getNwesContent().equals("")) {
				sql+=" and nwesContent like '%"+n.getNwesContent()+"%'";
			}
			if (n.getNewsCategoryId()!=0) {
				sql+=" and n.newsCategoryId="+n.getNewsCategoryId();
			}
			sql+=" and n.newsCategoryId=ne.newsCategoryId limit "+(page-1)*size+","+size;
			System.out.println(sql);//查看sql是否拼接成功
			// 4.执行sql语句
			rs = st.executeQuery(sql);// execute执行Query查询
			// 5.遍历结果集
			while (rs.next()) {
				int newsId = rs.getInt("newsId");
				String newsTitle = rs.getString("newsTitle");
				String nwesContent = rs.getString("nwesContent");
				int newsCategoryId = rs.getInt("newsCategoryId");
				String newsCategoryName= rs.getString("newsCategoryName");
				newsdetails = new Newsdetails(newsId, newsTitle, nwesContent, newsCategoryId, newsCategoryName);
				list.add(newsdetails);
			}
			return list;// 正常执行返回用户集合
		} catch (SQLException e) {// 异常
			e.printStackTrace();
			return null;// 异常执行返回空
		} finally {
			DBManager.closeAll(conn, st, rs);
		}
	}
	// 根据新闻详情id删除新闻详情
	// nid是新闻详情的id

	public int deleteNewsDetailsById(int nid) {
		return DBManager.executeUpdate("delete from newsdetails where newsId=" + nid);
	}
	/***
	 *添加新闻详情
	 *返回受影响行数 int
	 *
	 *
	 */
	public int addNewsDetails(Newsdetails ns) {
		return DBManager.executeUpdate("insert into newsdetails values (null,'"+ns.getNewsTitle()+"','"+ns.getNwesContent()+"',"+ns.getNewsCategoryId()+")");
	}
	/***
	 *查询新闻详情
	 *根据标题名
	 *
	 *
	 */
	public List<Newsdetails> getNewsdetailsByTitle(String name) {
		List<Newsdetails> list = new ArrayList<Newsdetails>();
		// 1.建立连接
		Connection conn = DBManager.getConnection();
		Statement st = null;
		ResultSet rs = null;
		Newsdetails newsdetails = null;
		try {// 正常
				// 2.创建Statement对象
			st = conn.createStatement();
			// 3.sql语句
			String sql = "select * from newsdetails where newsTitle='"+name+"'";
			// 4.执行sql语句
			rs = st.executeQuery(sql);// execute执行Query查询
			// 5.遍历结果集
			while (rs.next()) {
				int newsId = rs.getInt("newsId");
				String newsTitle = rs.getString("newsTitle");
				String nwesContent = rs.getString("nwesContent");
				int newsCategoryId = rs.getInt("newsCategoryId");
				newsdetails = new Newsdetails(newsId, newsTitle, nwesContent, newsCategoryId);
				list.add(newsdetails);

			}
			return list;// 正常执行返回用户集合
		} catch (SQLException e) {// 异常
			e.printStackTrace();
			return null;// 异常执行返回空
		} finally {
			DBManager.closeAll(conn, st, rs);
		}
	}
	/***
	 *查询新闻详情
	 *根据id
	 *用来修改的数据回显
	 *
	 */
	public Newsdetails getNewsDetailsById(int id) {
		
		// 1.建立连接
		Connection conn = DBManager.getConnection();
		Statement st = null;
		ResultSet rs = null;
		Newsdetails newsdetails = null;
		try {// 正常
				// 2.创建Statement对象
			st = conn.createStatement();
			// 3.sql语句
			String sql = "select * from newsdetails where newsId=" + id;
			// 4.执行sql语句
			rs = st.executeQuery(sql);// execute执行Query查询
			// 5.遍历结果集
			while (rs.next()) {
				int newsId = rs.getInt("newsId");
				String newsTitle = rs.getString("newsTitle");
				String nwesContent = rs.getString("nwesContent");
				int newsCategoryId = rs.getInt("newsCategoryId");
				newsdetails = new Newsdetails(newsId, newsTitle, nwesContent, newsCategoryId);
				

			}
			return newsdetails;// 正常执行返回用户集合
		} catch (SQLException e) {// 异常
			e.printStackTrace();
			return null;// 异常执行返回空
		} finally {
			DBManager.closeAll(conn, st, rs);
		}
	}
	/***
	 *修改新闻详情
	 *返回受影响行数 int
	 *
	 *
	 */
	public int updateNewsDetails(Newsdetails ns) {
		return DBManager.executeUpdate("UPDATE newsdetails SET newsTitle='"+ns.getNewsTitle()+"',nwesContent='"+ns.getNwesContent()+"' where newsId="+ns.getNewsId());
	}
	/***
	 *根据id批量删除新闻详情
	 *返回受影响行数 int
	 *
	 *
	 */
	public int delNewsdetailsByIds(String ids) {
		return DBManager.executeUpdate("delete from newsdetails where newsId in("+ids+")");
	}
}
